14. Quiz: Aliases for Multiple Window Functions

Shorten Your Window Function Queries by Aliasing

Window Function Alias

QUIZ QUESTION::

Using Derek's example in the previous window, deconstruct the window function alias into its two parts: the alias part and the window function part.

ANSWER CHOICES:



Part

Query text

WINDOW main_window AS

(PARTITION BY account_id ORDER BY DATE_TRUNC('month',occurred_at))

OVER (PARTITION BY account_id ORDER BY DATE_TRUNC('month',occurred_at))

OVER (PARTITION BY account_id ORDER BY DATE_TRUNC('month',occurred_at)) AS
SOLUTION:

Part

Query text

WINDOW main_window AS

(PARTITION BY account_id ORDER BY DATE_TRUNC('month',occurred_at))

Now, create and use an alias to shorten the following query (which is different than the one in Derek's previous video) that has multiple window functions. Name the alias account_year_window, which is more descriptive than main_window in the example above.

SELECT id,
       account_id,
       DATE_TRUNC('year',occurred_at) AS year,
       DENSE_RANK() OVER (PARTITION BY account_id ORDER BY DATE_TRUNC('year',occurred_at)) AS dense_rank,
       total_amt_usd,
       SUM(total_amt_usd) OVER (PARTITION BY account_id ORDER BY DATE_TRUNC('year',occurred_at)) AS sum_total_amt_usd,
       COUNT(total_amt_usd) OVER (PARTITION BY account_id ORDER BY DATE_TRUNC('year',occurred_at)) AS count_total_amt_usd,
       AVG(total_amt_usd) OVER (PARTITION BY account_id ORDER BY DATE_TRUNC('year',occurred_at)) AS avg_total_amt_usd,
       MIN(total_amt_usd) OVER (PARTITION BY account_id ORDER BY DATE_TRUNC('year',occurred_at)) AS min_total_amt_usd,
       MAX(total_amt_usd) OVER (PARTITION BY account_id ORDER BY DATE_TRUNC('year',occurred_at)) AS max_total_amt_usd
FROM orders

Workspace

This section contains either a workspace (it can be a Jupyter Notebook workspace or an online code editor work space, etc.) and it cannot be automatically downloaded to be generated here. Please access the classroom with your account and manually download the workspace to your local machine. Note that for some courses, Udacity upload the workspace files onto https://github.com/udacity, so you may be able to download them there.

Workspace Information:

  • Default file path:
  • Workspace type: sql-evaluator
  • Opened files (when workspace is loaded): n/a

Next Concept